package com.example.java251013_jdbc.JDBC;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public class StudentDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public List<Map<String, Object>> getAllStudents() {
        String sql = "SELECT * FROM student";
        return jdbcTemplate.queryForList(sql);
    }

    public int getTotalCount() {
        String sql = "SELECT COUNT(*) FROM student";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    public void addStudents() {
        String sql = "INSERT INTO student (studentNum, name, class, gender) VALUES (?, ?, ?, ?)";
        // 示例数据，替换为实际同学信息
        jdbcTemplate.update(sql, 2, "李四", "1", 1);
        jdbcTemplate.update(sql, 3, "王五", "2", 0);
        jdbcTemplate.update(sql, 4, "赵六", "3", 1);
    }

    public List<Map<String, Object>> getGenderCountByClass() {
        String sql = "SELECT class, gender, COUNT(*) as count FROM student GROUP BY class, gender";
        return jdbcTemplate.queryForList(sql);
    }
}